#include "stdafx.h"
#include "UtilSql.h"
#include "../Util.h"
CString CUtilSql::m_sName;
std::vector<CString> CUtilSql::m_vtTable;

bool CUtilSql::createDatabase(MYSQL* pSql,CString sDatabase)
{
	bool bRes = false;
	CString sNewQuery;
	sNewQuery.Format(_T("CREATE DATABASE %s"),sDatabase);			
	if (pSql)
	{
		CT2A ascii(sNewQuery, CP_UTF8);
		if(mysql_query(pSql,ascii.m_psz) == 0)
			 bRes = true;		
	}
	return bRes;
}

bool CUtilSql::createTable(CString sDatabase, CString sTable)
{
	bool bRes = false;
	CString sNewQuery;
	sNewQuery.Format(_T("CREATE TABLE %s.%s"),sDatabase,sTable);			
	if (conn)
	{
		CT2A ascii(sNewQuery, CP_UTF8);
		if(mysql_query(conn,ascii.m_psz) == 0)
			bRes = true;		
	}
	return bRes;
}
bool CUtilSql::importFromFile(CString sFile)
{
	bool bRes = false;
	CStdioFile initFile;
	if(initFile.Open(sFile,CFile::modeRead))
	{
		CString sLine = _T("");
		CString sKeyPrevious = _T("");
		CString sQueryFull;
		while(sLine != _T("[END]"))
		{
			initFile.ReadString(sLine);
			if (sLine == _T("[TABLE]"))
			{
				sKeyPrevious = sLine;
				initFile.ReadString(sLine);
			}		
			if (sKeyPrevious == _T("[TABLE]"))
			{

				if (sLine == _T("[/TABLE]"))
				{
					bRes = createTable(g_sDatabase,sQueryFull);
					sQueryFull = _T("");
					sKeyPrevious = _T("");
				}
				else
					sQueryFull += sLine;
			}

		}
		initFile.Close();
	}
	else
		AfxMessageBox(_T("Read file init error"));
	return bRes;
}
bool CUtilSql::connectToServer(const CString &sHost, const CString& sUser, const CString& sPass)
{
	MYSQL* pSql = NULL;
	pSql = mysql_init(NULL);
	if (conn == NULL) 
		return false;
	else if (mysql_real_connect(pSql, CUtil::convertStringToChar(sHost), CUtil::convertStringToChar(sUser),
								CUtil::convertStringToChar(sPass), NULL, 3306,"", 0) == NULL)
		return false;
	return true;
}
bool CUtilSql::connectToDatabase()
{
	conn = mysql_init(NULL);
	if (conn == NULL) 
		return false;
	else if (mysql_real_connect(conn, CUtil::convertStringToChar(g_sHost), CUtil::convertStringToChar(g_sUser),
		CUtil::convertStringToChar(g_sPass), CUtil::convertStringToChar(g_sDatabase), 3306,"", 0) == NULL)
		return false;
	getTableList(m_vtTable);
	return true;
}
bool CUtilSql::insertRecord(int nTable,CString sValue)
{
	bool bResult = false;
	if (mysql_ping(conn)== 0)
	{
		std::vector<CString> vtField;
		std::vector<int> vtType;
		if(getField(nTable,vtField,vtType))
		{
			CString sHost = g_sDatabase + _T(".");
			sHost+= getTableAt(nTable);
			CString sNewQuery = _T("INSERT INTO ") + sHost;
			sNewQuery += _T(" ( ");
			for (int i =0; i < vtField.size(); i++)
			{
				sNewQuery = sNewQuery + vtField.at(i) + _T(",");
			}
			sNewQuery = sNewQuery.Mid(0,sNewQuery.GetLength() -1);
			sNewQuery = sNewQuery + _T(") VALUES ");
			sNewQuery += sValue;
			CT2A ascii(sNewQuery, CP_UTF8);
			int nError = mysql_query(conn,ascii.m_psz);
			if (nError == 0)
			{
				return bResult = true;
			}
		}
	}
	return bResult;
}
bool CUtilSql::updateRecord(CString sQurey)
{
	return false;
}
bool CUtilSql::deleteRecord(CString sQurey)
{
	return false;
}
bool CUtilSql::getField(int nPosTbl,std::vector<CString> &vtField , std::vector<int> &vtType)
{
	std::map<CString,int> mFields;
	char sQuery[400];
	CString query = _T("SELECT * FROM ")+m_vtTable.at(nPosTbl);
	wcstombs(sQuery, (TCHAR*)(const TCHAR*)query, 400);
	mysql_query(conn,sQuery);
	MYSQL_RES * pResult = mysql_use_result(conn);
	bool bResult = false;
	if (pResult)
	{
		CStringArray arrField;
		MYSQL_ROW row;
		if (pResult)
		{
			row = mysql_fetch_row(pResult);
			for (int i =0; i< pResult->field_count; i++)
			{
				MYSQL_FIELD *field = mysql_fetch_field_direct(pResult,i);
				if (field)
				{
					CString sFiledName;
					sFiledName = field->name;
					vtField.push_back(sFiledName);
					vtType.push_back(field->type);
				}
			}
			bResult = true;
		}
		mysql_free_result(pResult);//You must also call mysql_free_result() after you are done with the result set. 
		
	}
	return bResult;
}
bool CUtilSql::getTableList(vector<CString> &vtTables)
{
	MYSQL_RES * pResult = mysql_list_tables(conn,NULL);
	bool bResult = false;
	if (pResult)
	{
		CStringArray arrField;
		MYSQL_ROW row;
		while((row = mysql_fetch_row(pResult)) )
		{
			CString sTable = CString(row[0]);
			vtTables.push_back(sTable);
		}
		mysql_free_result(pResult);//You must also call mysql_free_result() after you are done with the result set. 
		bResult = true;
	}
	return bResult;
}

CString  CUtilSql::getTableAt(int nPos)
{
	CString sResult = _T("");
	if (m_vtTable.size() > 0)
	{
		sResult = m_vtTable.at(nPos);
	}

	return sResult;
}
CString CUtilSql::getFielName(const CString& sTable,int nPos)
{
	CString sResult = _T("");
	//MYSQL_RES *mysql_list_fields(MYSQL *mysql, const char *table, const char *wild)

	return sResult;
}
CString CUtilSql::getPrimerKey()
{
	CString sPrimerKey = _T("");
	char sQuery[400];
	CString query;
	query.Format(_T("SHOW INDEXES FROM personalmanager.%s WHERE Key_name = 'PRIMARY'"),m_sName);
	wcstombs(sQuery, (TCHAR*)(const TCHAR*)query, 400);
	mysql_query(conn,sQuery);
	MYSQL_RES * pResult = mysql_use_result(conn);
	if (pResult)
	{
		MYSQL_ROW row = mysql_fetch_row(pResult);
		if (row)
		{
			sPrimerKey = row[4];
		}
		mysql_free_result(pResult);//You must also call mysql_free_result() after you are done with the result set. 

	}
	return sPrimerKey;
}

CString CUtilSql::getFormatFromType(int nType)
{
	CString sFormat = _T("");
	
	if (nType == T_INT)
	{
		sFormat = _T("%d");
	}
	else if (nType == T_DOUBLE)
	{
		sFormat = _T("");
	}
	else if (nType == T_STRING)
	{
		sFormat = _T("'%s'");
	}
	else if (nType == UTF_STRING)
	{
		sFormat = _T("_utf8'%s'");
	}
	return sFormat;
}

bool CUtilSql::deleteDatabase(const CString& sDatabase)
{
	MYSQL mysql;
	bool bRes = false;
	if (conn)
	{
		CString sNewQuery;
		sNewQuery.Format(_T("DROP DATABASE %s"),sDatabase);			
		if (conn)
		{
			CT2A ascii(sNewQuery, CP_UTF8);
			if(mysql_query(conn,ascii.m_psz) == 0)
				bRes = true;		
		}
	}	
	return bRes;
}